package com.cn.dao;

import com.cn.bean.Worker;
import com.cn.util.JDBCUtils;
import com.cn.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Wrapper;
import java.util.ArrayList;

public class WorkerDao {
    /**
     * 添加员工
     */
    public int add(Worker worker){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int result = 0;
        try {
            String sql="insert into goods_worker values(null,?,?,?,?,?,?)";
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, worker.getWorkerName());
            preparedStatement.setString(2, worker.getWorkerNumber());
            preparedStatement.setString(3, worker.getWorkerSex());
            preparedStatement.setString(4, worker.getDept());
            preparedStatement.setInt(5, worker.getDepartmentID());
            preparedStatement.setString(6, worker.getWorkerAddress());
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.toString());
        } finally {
            JDBCUtils.release(connection, preparedStatement, null);
        }
        return result;
    }

    /**
     * 查询员工
     */
    public ArrayList<Worker> query(Worker worker){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Worker> list = new ArrayList<>();
        try {
            String basicSQL = "select * from goods_worker AS worker INNER JOIN goods_department AS department ON worker.departmentID=department.deptId";
            StringBuffer stringBuffer=new StringBuffer(basicSQL);
            if(worker!=null && !StringUtils.isEmpty(worker.getWorkerName())){
                stringBuffer.append(" where worker.workerName like "+"'%"+worker.getWorkerName()+"%'");
            }
            if(worker!=null && !StringUtils.isEmpty(worker.getWorkerNumber())){
                stringBuffer.append(" and worker.workerNumber like "+"'%"+worker.getWorkerNumber()+"%'");
            }
            if(worker!=null && worker.getDepartmentID()>0){
                stringBuffer.append(" and worker.departmentID="+worker.getDepartmentID());
            }
            String sql = stringBuffer.toString();
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Worker wk = new Worker();
                wk.setWorkerID(resultSet.getInt("workerID"));
                wk.setWorkerName(resultSet.getString("workerName"));
                wk.setWorkerNumber(resultSet.getString("workerNumber"));
                wk.setWorkerSex(resultSet.getString("workerSex"));
                wk.setDept(resultSet.getString("dept"));
                wk.setWorkerAddress(resultSet.getString("workerAddress"));
                wk.setDepartmentName(resultSet.getString("deptName"));
                System.out.println(wk.getDepartmentName());
                list.add(wk);
            }
        } catch (Exception e) {
            System.out.println(e.toString());
            System.out.println("test");
        } finally {
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 删除员工
     */
    public int delete(int workerID){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int result = 0;
        try {
            String sql="delete from goods_worker where workerID=?";
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, workerID);
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.toString());
        } finally {
            JDBCUtils.release(connection, preparedStatement, null);
        }
        return result;
    }

    /**
     * 修改员工信息
     */
    public int update(Worker worker){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int result = 0;
        try {
            String sql="update goods_worker set workerName=?,workerNumber=?,workerSex=?,departmentID=?,dept=?,workerAddress=? where workerID=?";
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, worker.getWorkerName());
            preparedStatement.setString(2, worker.getWorkerNumber());
            preparedStatement.setString(3, worker.getWorkerSex());
            preparedStatement.setInt(4, worker.getDepartmentID());
            preparedStatement.setString(5, worker.getDept());
            preparedStatement.setString(6, worker.getWorkerAddress());
            preparedStatement.setInt(7, worker.getWorkerID());
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.toString());
        } finally {
            JDBCUtils.release(connection, preparedStatement, null);
        }
        return result;
    }

    /**
     * 判断某部门中是否存在员工
     */
    public boolean isHaveStudentInClass(int departmentID){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        boolean result = false;
        try {
            String sql="select * from goods_worker where departmentID=?";
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, departmentID);
            resultSet=preparedStatement.executeQuery();
            result = resultSet.next();
        } catch (Exception e) {
            System.out.println(e.toString());
        } finally {
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
        if(result){
            System.out.println("该部门中存在员工，所以不能删除该部门");
        }else{
            System.out.println("该部门中不存在员工，所以可以删除该部门");
        }
        return result;
    }
}
